合并数据

本页目录

与数据拆分相反，常常需要将多个文件，或多个DataFrame的数据进行合并。合并数据，可以是简单拼接数据，也可以是根据关联变量进行合并。本节介绍后者。[大谦Excel，dqexcel点com]

合并工作表

【问题描述】

将一个工作簿中不同工作表的数据根据指定的关联变量合并到一个Excel文件。

【示例4-4】

本例使用的Excel文件的完整路径为“D:/Samples/ch04/02 数据合并/合并工作表/学生成绩.xlsx”。该文件打开后如图4-3所示，工作簿中有3个工作表，分别为同一批同学的语文、数学和英语成绩。要求将3个工作表的数据合并后保存到一个新的Excel文件。

Document Image

图4-3 合并不同工作表的数据

  • 编写下面的代码：
code.python 
import pandas as pd
# 读取3个工作表的数据
df1 = pd.read_excel('D:/Samples/ch04/02 数据合并/合并工作表/学生成绩.xlsx', sheet_name='Sheet1', index_col=0, engine='openpyxl')
df2 = pd.read_excel('D:/Samples/ch04/02 数据合并/合并工作表/学生成绩.xlsx', sheet_name='Sheet2', index_col=0, engine='openpyxl')
df3 = pd.read_excel('D:/Samples/ch04/02 数据合并/合并工作表/学生成绩.xlsx', sheet_name='Sheet3', index_col=0, engine='openpyxl')
# 合并3个工作表的数据
merged_df = pd.merge(df1, df2, on='姓名', how='outer')
merged_df = pd.merge(merged_df, df3, on='姓名', how='outer')
#输出合并结果
print(merged_df)
# 将合并后的数据保存到Excel文件
writer = pd.ExcelWriter('D:/Samples/ch04/02 数据合并/合并工作表/学生成绩_合并.xlsx', engine='openpyxl')
merged_df.to_excel(writer, index=True)
writer.save()

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，实现数据的合并，在IDLE Shell窗口输出合并后的结果。

code.python 
>>> == RESTART: D:/Samples/1.py =
       语文     数学     英语
姓名
王东   16.0   27.0   34.0
徐慧   85.0   54.0    NaN
王慧琴  99.0   73.0    NaN
章思思  95.0    NaN   62.0
阮锦绣  92.0    NaN   92.0
周洪宇  93.0   92.0  113.0
谢思明  98.0   95.0  117.0
程成   98.0   95.0  114.0
王洁    NaN  102.0  136.0
张丽君   NaN  104.0  105.0

可见，合并方式为”outer”时合并的结果是各数据集的并集。

可在原始数据文件的相同路径下找到新建的Excel文件。

【知识点扩展】

本例使用pandas的merge函数合并数据。该函数一次只能合并2个DataFrame，所以代码中连续用了两次merge函数。merge函数的主要参数的意义如表4-1所示。

表4-1 merge函数的主要参数

参 数 说 明
left DataFrame数据1
right DataFrame数据2
how 数据合并的方式，有inner(内连接)、outer(外连接)、left(左连接)和right(右连接)4种，默认时为inner
on 指定用于连接的列索引标签。如果没指定且其他参数也没有指定，用两个DataFrame的列索引标签交集作为连接键
left_on 指定左侧DataFrame用作连接键的列索引标签
right_on 指定右侧DataFrame用作连接键的列索引标签
left_index 值为True时指定左侧DataFrame的行索引作为连接键，默认值为False
right_index 值为True时指定右侧DataFrame的行索引作为连接键，默认值为False
sort 默认值为True，对合并后的数据进行排序；设置为False，取消排序
suffixes 两个DataFrame中如果存在除连接键以外的同名索引标签，合并后指定不同后缀进行区分，默认时为("_x","_y")

merge函数的使用，有几个关键内容要把握，即连接键的设置、连接键的数量关系和连接方式的设置。

  • 连接键的设置

merge函数提供了类似于关系数据库连接的操作，可以根据一个或多个键将两个DataFrame数据连接起来。当进行连接的两个DataFrame有相同的列索引标签时，使用merge方法的on参数设置连接键。

如果用作连接键的索引列具有不同的标签，比如一个是“准考号”，另一个是“准考证”，它们表达的是一个意思。此时就不能用on参数进行设置，而是用left_on,参数和right_on参数分别设置两个DataFrame的连接键，即left_on= "准考号", right_on= "准考证"。

设置left_index参数或right_index参数的值为True时，指定左侧或右侧DataFrame的行索引作为连接键。适用于一个DataFrame的行索引与另一个DataFrame的索引列可用于连接的情况。

  • 连接键的数量关系

根据连接键索引列中值的重复情况，可以有1对1、1对多、多对1和多对多等几种数量关系。本例中，连接的两个DataFrame中连接键“姓名”列中的值都是唯一的，没有出现重复的情况，这种情况称为1对1的数量关系。如果至少一个DataFrame中的值有重复，就会出现1对多、多对1或多对多的情况，这里不展开介绍。

  • 连接方式

用how参数设置连接键连接的方式。有内连接(inner)、外连接(outer)、左连接(left)和右连接(right)等4种连接方式，它们对应的集合关系如图4-4所示。

Document Image

图4-4 各连接方式对应的集合关系

本例中设置how参数的值为”outer”，得到的是各数据集的并集。

设置how参数的值为”inner”时，进行内连接，得到的是各数据集的交集。将本例代码中how参数的值改为”inner”，即

code.python 
merged_df = pd.merge(df1, df2, on='姓名', how='inner')
merged_df = pd.merge(merged_df, df3, on='姓名', how='inner')

运行代码后输出的合并输入如下所示。

code.python 
>>> == RESTART: D:/Samples/1.py =
     语文  数学   英语
姓名
王东   16  27   34
周洪宇  93  92  113
谢思明  98  95  117
程成   98  95  114

可见，得到的合并结果是各数据集的交集。

设置how参数的值为”left”时，进行左连接。将本例代码中how参数的值改为”left”，即

code.python 
merged_df = pd.merge(df1, df2, on='姓名', how='left')
merged_df = pd.merge(merged_df, df3, on='姓名', how='left')

运行代码后输出的合并输入如下所示。

code.python 
>>> == RESTART: D:/Samples/1.py =
     语文    数学     英语
姓名
王东   16  27.0   34.0
徐慧   85  54.0    NaN
王慧琴  99  73.0    NaN
章思思  95   NaN   62.0
阮锦绣  92   NaN   92.0
周洪宇  93  92.0  113.0
谢思明  98  95.0  117.0
程成   98  95.0  114.0

用左连接方式合并两个DataFrame时，合并结果是保持左侧的DataFrame不变，再并上两个DataFrame的交集。

设置how参数的值为”right”时，进行右连接。将本例代码中how参数的值改为”right”，即

code.python 
merged_df = pd.merge(df1, df2, on='姓名', how='right')
merged_df = pd.merge(merged_df, df3, on='姓名', how='right')

运行代码后输出的合并输入如下所示。

code.python 
>>> == RESTART: D:/Samples/1.py =
       语文     数学   英语
姓名
王东   16.0   27.0   34
章思思   NaN    NaN   62
阮锦绣   NaN    NaN   92
周洪宇  93.0   92.0  113
谢思明  98.0   95.0  117
程成   98.0   95.0  114
王洁    NaN  102.0  136
张丽君   NaN  104.0  105

用左连接方式合并两个DataFrame时，合并结果是保持右侧的DataFrame不变，再并上两个DataFrame的交集。

  • 有非键列标签重复的情况

进行合并的两个DataFrame如果都有非键列标签，比如“身高”，则合并以后，为了进行区分，会自动给左侧DataFrame中的“身高”添加了后缀"_x"，给右侧DataFrame中的“身高”添加了后缀"_y"。这是默认设置。如果需要自定义后缀，可以用suffixes参数进行设置。如下面的语句设置用“姓名”连接时，如果有非键列标签，则左侧的标签添加后缀”_l”，右侧的标签添加后缀”_r”。

code.python 
df3=pd.merge(df1,df2,on= "姓名",suffixes=("_l","_r"))

合并工作簿

【问题描述】

与合并不同工作表中的数据类似，只是现在数据是保存在不同Excel工作簿文件中的。

【示例4-5】

本例使用的Excel文件的完整路径为“D:/Samples/ch04/02 数据合并/合并工作簿/“，有3个Excel文件：学生成绩_语文.xlsx、学生成绩_数学.xlsx和学生成绩_英语.xlsx，如图4-5所示。要求以“姓名”为关联变量，用merge函数合并3个工作簿的数据并保存到Excel文件。

Document Image

图4-5 给定的3个Excel工作簿文件

  • 编写下面的代码：
code.python 
import pandas as pd
# 读取3个Excel文件
df_chinese = pd.read_excel('D:/Samples/ch04/02 数据合并/合并工作簿/学生成绩_语文.xlsx', index_col=0, engine='openpyxl')
df_math = pd.read_excel('D:/Samples/ch04/02 数据合并/合并工作簿/学生成绩_数学.xlsx', index_col=0, engine='openpyxl')
df_english = pd.read_excel('D:/Samples/ch04/02 数据合并/合并工作簿/学生成绩_英语.xlsx', index_col=0, engine='openpyxl')
# 合并3个DataFrame，以“姓名”为关联变量
df_merge = pd.merge(df_chinese, df_math, on='姓名', how='outer')
df_merge = pd.merge(df_merge, df_english, on='姓名', how='outer')
# 将新表格保存到Excel文件中
writer = pd.ExcelWriter('D:/Samples/ch04/02 数据合并/合并工作簿/学生成绩_合并.xlsx',
                        engine='openpyxl')
df_merge.to_excel(writer, sheet_name='Sheet1')
writer.save()

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，实现数据的合并。可在原始数据文件的相同路径下找到新建的Excel文件。